package com.sky.unit;

import com.sky.domain.User;

import java.io.InputStream;
import java.io.InputStreamReader;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * @author chengjy
 * @date 2023/4/29
 */
public class JdbcUnits {
    static {
        try {
            //1. 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //类加载器
            ClassLoader contextClassLoader = Thread.currentThread().getContextClassLoader();
            //加载器资源下文件
            InputStream inputStream = contextClassLoader.getResourceAsStream("mysqlConfig.properties");
//        InputStream inputStream =NioFileTest.class.getClassLoader().getResourceAsStream("abc.properties");
            System.out.println("初始化jdbc驱动...");
            Properties prop = new Properties();
            //转字符流
            prop.load(new InputStreamReader(inputStream));
            url = prop.getProperty("url");
            username = prop.getProperty("username");
            password = prop.getProperty("password");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static String url ;
    private static String username ;
    private static String password ;

    /**
     * 增删改工具方法
     */
    public void cudIkun(String sql, List<String> keyList, Map<String,Object> param) throws SQLException {
        //1、获取数据库连接IO
        Connection connection = DriverManager.getConnection(url, username, password);
        PreparedStatement ps = connection.prepareStatement(sql);
        //2、给参数设置值
        for (int i = 0; i < keyList.size(); i++) {
            ps.setObject(i+1,param.get(keyList.get(i)));
        }
        //3、执行数据库操作
        ps.executeUpdate();
        connection.close();
        ps.close();
    }

    /**
     * 查询方法
     * @param sql
     * @param calzz
     * @param value
     * @return
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    private static Object selectIkun(String sql,Class calzz,Object ... value) throws SQLException, InstantiationException, IllegalAccessException {
        Connection connection = DriverManager.getConnection(url, username, password);
        // 给sql字段赋值
        sql = fieldFill(sql,value);
        System.err.println(sql);
        //4、获取连接参数 statement  理解为一个io
        PreparedStatement ps = connection.prepareStatement(sql);
        //5、执行数据库操作
        ResultSet resultSet = ps.executeQuery();
        //5.1、解析返回值
        Object o = analysisReturn(resultSet, calzz);

        //6、资源关闭
        resultSet.close();
        ps.close();
        return o;
    }

    /**
     * 解析resultSet中单个实体的返回值
     * @param resultSet
     * @param calzz
     * @return
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws SQLException
     */
    private static Object analysisReturn(ResultSet resultSet, Class calzz) throws IllegalAccessException, InstantiationException, SQLException {
        //1.通过反射生成对象
        Object obj = calzz.newInstance();
        //2.获取所有字段对象名，并正对不同字段的class进行不同的方法判断
        Field[] fields = calzz.getDeclaredFields();
        //创建返回容器
        List objList = new ArrayList();
        //读取一行数据则只需if执行一次r.next即可，如果要多行则需用while
        while (resultSet.next()) {
            for (Field field : fields) {
                //2.1、拿到字段的属性名
                String fieldName = field.getName();
                if ("serialVersionUID".equals(fieldName)) continue;
                //2.2、驼峰转下划线方法
                fieldName = tuoFenghuaTransition(fieldName);
                //3.对象所有属性使用暴力反射赋值
                field.setAccessible(true);
                field.set(obj, resultSet.getObject(fieldName));
            }
            objList.add(obj);
        }
        //4.返回生成的对象
        return objList;
    }

    /**
     * 驼峰转下划线方法
     * @param fieldName
     * @return
     */
    private static String tuoFenghuaTransition(String fieldName) {
        char[] ch = fieldName.toCharArray();
        for (int i = 0; i < ch.length; i++) {
            if(ch[i] >= 'A' && ch[i] <= 'Z'){
                String x ="_" + (char)(ch[i] + 32);
                fieldName = fieldName.replace(String.valueOf(ch[i]), x);
            }
        }
        return fieldName;
    }

    /**
     * 给占位符赋值方法
     * @param sql
     * @param value
     * @return
     */
    private static String fieldFill(String sql, Object ... value) {
        //1.解析sql中的？，出现一个替换为对饮value数组中索引-1的位置
        int index = 0;
        while (sql.contains("?")){
            Object o = value[index++];
            String val = "'"+o+"'";
            sql = sql.replaceFirst("[?]", val);
        }
        return sql;
    }

    /**
     * jdbc查询单条方法
     * @param sql
     * @param keyList
     * @param map
     * @param clazz
     * @param <T>
     * @return
     */
    public <T> T selectIkun(String sql, List<String> keyList, Map<String, Object> map, Class clazz) throws SQLException, InstantiationException, IllegalAccessException {
        //1、获取数据库连接IO
        Connection connection = DriverManager.getConnection(url, username, password);
        //4、获取连接参数 statement  理解为一个io
        PreparedStatement ps = connection.prepareStatement(sql);
        //5、给占位符赋值
        for (int i = 0; i < keyList.size(); i++) {
            ps.setObject(i+1, map.get(keyList.get(i)));
        }
        //5、执行数据库操作
        ResultSet resultSet = ps.executeQuery();
        //5.1、解析返回值
        Object o = analysisReturn(resultSet, clazz);

        //6、资源关闭
        connection.close();
        ps.close();
        resultSet.close();
        return (T) o;
    }
}
